#import sqlite3
import csv
import sys
#from db_common import table_info
sys.path.append("../") 
import GUIconfig as config
import sys
import os
#import GUIconfig
#import efetch
from partition_modify import partition_trim 
#from monet_backend import execute_query, execute_multiple
#import monet_backend as dbms_backend
#from monet_backend import execute_query, execute_multiple
#import monet_backend as dbms_backend
import sqlite_backend as dbms_backend

#from sqlite_backend import execute_query, execute_multiple

 
#
#def db_connect(db_path):
#    return sqlite3.connect(db_path)
#
#def execute_query(query, db_path, values=None):
#    
#    output_list = ""
#    try :
#        print "query", query
#        conn = db_connect(db_path)
#        cursor = conn.cursor()
#        if values:
#            cursor.execute(query, values) # values are inserted on the '?'. sqlite3 doc says this is safer
#        else:
#            cursor.execute(query)
#        #print "Executing query ", query
#        conn.commit()
#        
#        output_list = cursor.fetchall()
#        
#        #output
#    except sqlite3.Error, e:
#        print str(e)
#        #raise e
#    finally:
#        cursor.close()
#    
#    return output_list
#
#def execute_multiple(queries, db_path):
#    #output_list = ""
#    conn = db_connect(db_path)
#    #cursor = conn.cursor()
#    #conn.set_autocommit(False)
#    #sql_script = """
#    #START TRANSACTION;
#    #%s;
#    #COMMIT;
#    #""" % ";".join(queries) 
#        #exec_queries()
#    #finished = True
#    while 1 :
#        finished = True 
#        for query in queries:
#    
#            try:
#                conn.execute(query)
#    
#            except sqlite3.Error, e:
#            #    
#                print str(e)
#                print "removing query %s and restarting" % query
#                conn.rollback()
#                queries.remove(query)
#                finished = False
#                
#                break
#        if finished:
#            break
#        #finished = True
#        #try:
#    conn.commit()
#        #except monetdb.sql.Error, e: 
#          
#    #conn.commit()
#    #cursor.close()
#    #conn.set_autocommit(True)
#    conn.close()
#        
#    return #output_list

def use_monetdb():
    global dbms_backend
    import monet_backend as dbms_backend 

def use_sqlite():
    global dbms_backend
    import sqlite_backend as dbms_backend 

def create_snp_gene_table(project_db_path):
    primary_key = "id"
    table_attributes = []
    table_attributes.append("%s varchar(20) primary key" % primary_key) 
    
    #for attr in attributes[1:]: # first one is the key we already provided
    #    table_attributes.append("%s %s" % (attr, field_datatype)) 
    table_attributes.append("gene varchar(30) default 'not updated'")
    table_attributes.append("chromosome varchar(15) default '-1'")
    #table_attributes.append()
    
    attributes_str = ", ".join(table_attributes)
    query = "create table %s (%s)" % (config.snp_gene_table, attributes_str) 
    
    dbms_backend.execute_query(query, project_db_path)
    #print "created DB : \n", query

def create_table(table_name, project_db_path, attributes, primary_key, primary_key_datatype, field_datatype):
    dbms_datatype = field_datatype
    table_attributes = []
    table_attributes.append("%s %s primary key" % (primary_key, primary_key_datatype)) 
    
    for attr in attributes[1:]: # first one is the key we already provided
        table_attributes.append("%s %s" % (attr, dbms_datatype)) 
    attributes_str = ", ".join(table_attributes)
    query = "create table %s (%s)" % (table_name, attributes_str) 
    print "Create DB query", query
    dbms_backend.execute_query(query, project_db_path)
    #print "created DB : \n ", query


def validate_and_format_header(header):
    """
    Strip header fields
    """
    for i in xrange(len(header)):
        if header[i].strip() == "":
            raise Exception("header error. empty header field %s " % str(header))
        else :
            header[i] = header[i].strip()
        
    return header

def validate_row(row, num_columns):
    if not row:
        # TODO : throw more precise exception
        raise Exception("row empty")
    
    if len(row) != num_columns:
        # TODO : throw more precise exception
        raise Exception("missing columns")


def format_row(row, datatype):
    if datatype in ("integer", "tinyint"):
        conv_funct = int
    elif datatype == "real":
        conv_funct = float
        
    #clean_row = []
    id_field =row[0].strip()
    if id_field == "":
        # TODO : throw more precise exception
        raise Exception("ID field empty")
    #clean_row.append(int(id_field))
    row[0] = int(id_field)
    for i in xrange(1,len(row)):
        field = row[i].strip()
        if field == "" or field=="Bad":
            #clean_row.append(conv_funct(-1))
            row[i] = conv_funct(-1)
        else:
            #clean_row.append(conv_funct(field))
            row[i] = conv_funct(field)
    return row


def import_from_file(source_file_path, destination_table, project_db_path, primary_key_datatype, field_datatype, progress_callback=None):
    update_interval_per_read_lines = 25# update after this many read lines
    INSERTS_PER_TRANSACTION = 25
    #BYTES_PER_TRANSACTION = 1000
    f_obj = open(source_file_path)
    
    #total_read_bytes = 0
    #position_on_commit = 0
    #read_bytes_since_commit = 0
    
    file_size = os.stat(source_file_path).st_size
    reader = csv.reader(f_obj, delimiter='\t', quoting=csv.QUOTE_ALL)
    
    header = reader.next()
    print "header len", len(header)
    
    attributes = validate_and_format_header(header)
    num_fields = len(attributes)
    print "num_fields", num_fields, attributes
    primary_key = "id"
    create_table(destination_table, project_db_path, attributes, primary_key, primary_key_datatype, field_datatype)
    
    queries = []
    
    for row in reader:
        validate_row(row, num_fields)
        #formatted_row = genotype_format_row(row)
        formatted_row = format_row(row, field_datatype)
        query = "insert into %s values %s" % (destination_table, str(tuple(formatted_row))) 
        #print repr(query)
        #print repr(formatted_row)
        #print "row len", len(formatted_row)
          
        queries.append(query)
        
        #total_read_bytes = f_obj.tell() # find out how much we have read 
        
        #read_bytes_since_commit = total_read_bytes - position_on_commit 
        
        #if read_bytes_since_commit > BYTES_PER_TRANSACTION :
        #    position_on_commit = total_read_bytes
        #    read_bytes_since_commit = 0
        #    dbms_backend.execute_multiple(queries, project_db_path)
        #    queries = []
        #    

        if len(queries) == INSERTS_PER_TRANSACTION:
            print "writing ", len(queries)
            dbms_backend.execute_multiple(queries, project_db_path)
            queries = []
            #import time
            #time.sleep(10)
            #print "row len", len(formatted_row)
            #print repr(row)
            #print repr(formatted_row)
            #print repr(query)
        #execute_query(query, project_db_path)
        if progress_callback and reader.line_num % update_interval_per_read_lines == 0: # update progress every Nth line
            #row_size = bytearray(" ".join(row))#sys.getsizeof(" ".join(row))
            
            read_bytes = f_obj.tell() # find out how much we have read
            progress_percentage = int(float(read_bytes)/float(file_size)*100)
            
            #print read_bytes, file_size, progress_percentage
            msg = "wrote %i rows" % (reader.line_num)
            exit_signal = progress_callback(progress_percentage, msg)
            if exit_signal:
                f_obj.close()
                return False

    # write the last bulk
    if len(queries) > 0:
        print "writing last", len(queries)
        dbms_backend.execute_multiple(queries, project_db_path)
        
    f_obj.close()
    
    return True


def import_genotypes(genotype_file_path, project_db_path, progress_callback=None):
    return import_from_file(source_file_path=genotype_file_path, 
                     destination_table=config.genotype_table, 
                     project_db_path=project_db_path, 
                     progress_callback=progress_callback, 
                     primary_key_datatype="integer", 
                     field_datatype="tinyint")
    
    
def import_phenotypes(phenotype_file_path, project_db_path, progress_callback=None):
    return import_from_file(source_file_path=phenotype_file_path, 
                     destination_table=config.phenotype_table, 
                     project_db_path=project_db_path, 
                     progress_callback=progress_callback, 
                     primary_key_datatype="integer", 
                     field_datatype="real")

def import_classes(class_file_path, project_db_path, progress_callback=None):
    return import_from_file(source_file_path=class_file_path, 
                     destination_table=config.class_table, 
                     project_db_path=project_db_path, 
                     progress_callback=progress_callback, 
                     primary_key_datatype="integer", 
                     field_datatype="tinyint")

def import_class_by_partition(partition_file_path, partition_threshold, partition_dir, project_db_path, progress_callback=None):
    
    progress_callback(1)
    class_file = partition_trim(partition_file_path, partition_threshold, partition_dir)
    progress_callback(2)
    return import_from_file(source_file_path=class_file, 
                     destination_table=config.class_table, 
                     project_db_path=project_db_path, 
                     progress_callback=progress_callback, 
                     primary_key_datatype="integer", 
                     field_datatype="tinyint")


def import_genotype_names(genotype_file_path, project_db_path, progress_callback=None):
    INSERTS_PER_TRANSACTION = 500
    f_obj = open(genotype_file_path)
    reader = csv.reader(f_obj, delimiter='\t', quoting=csv.QUOTE_ALL)
    
    header = reader.next()
    snp_names = header[1:] # the first field is the id
    #print snp_names
    #print header
    #attributes = ["id", "gene", "chromosome"]
    #attributes = validate_and_format_header(header)
    
    #num_fields = len(attributes)
    #primary_key = "id"
    create_snp_gene_table(project_db_path)
    queries = []
    for i in xrange(len(snp_names)): # the first field is the id
        queries.append("insert into %s(id) values ('%s')" % (config.snp_gene_table, snp_names[i]))
        
        if i % INSERTS_PER_TRANSACTION == 0:
            dbms_backend.execute_multiple(queries, project_db_path)
            queries = []
            percentage = int(float(i)/float(len(snp_names))*100)
            exit_signal = progress_callback(percentage)
            if exit_signal:
                return False
            
    
    if queries: # write the rest    
        dbms_backend.execute_multiple(queries, project_db_path)
        
    return True

def import_genes(snp_gene_info_list, db_path):
    queries = []
    for snp_info in snp_gene_info_list:
        #query = "insert into %s(Gene, chromosome) values (%s, %s) where id = '%s'" % (config.snp_gene_table, snp_info[1], snp_info[2], snp_info[0])
        values = {"table":config.snp_gene_table, "gene":snp_info[1], "chromosome_nr":snp_info[2], "id":snp_info[0]}
        query = "update %(table)s set Gene='%(gene)s', chromosome='%(chromosome_nr)s' where id = '%(id)s'" % values
        queries.append(query)
    dbms_backend.execute_multiple(queries, db_path)
    return True
    

def extract_genotype_info(db_path):
    query = "select id, gene, chromosome from %s" % config.snp_gene_table
    #if names :
    #    query += " where id in (%s)" % ", ".join(names)
    output = dbms_backend.execute_query(query, db_path)
    #snp_list = [t[0] for t in output] 
    
    return output#snp_list
    
def extract_genotypes(db_path, names=None):
    query = "select * from %s" % config.genotype_table
    if names :
        query += " where id in (%s)" % ", ".join(names)  
    return dbms_backend.execute_query(query, db_path)


def extract_gene_snps(db_path, gene_name):
    query = "select id from %s" % config.snp_gene_table
    
    query += " where gene='%s'" % gene_name# % ", ".join(names)
    
    output = dbms_backend.execute_query(query, db_path)
    
    output_list = [snp_tuple[0] for snp_tuple in output]
    
    return output_list


def extract_genes(db_path, names):
    query = "select distinct gene from %s" % config.snp_gene_table
    if names :
        query += " where id in (%s)" % ", ".join(names)
    query += " order by gene"
    output = dbms_backend.execute_query(query, db_path)
    output_list = [gene_tuple[0] for gene_tuple in output]
    #print output_list  
    return output_list

#def extract_genes_based_on_snps(db_path, snp_names):
#    query = "select distinct gene from %s" % config.snp_gene_table
#    if snp_names :
#        query += " where  in (%s)" % ", ".join(snp_names)
#    output = execute_query(query, db_path)
#    output_list = [gene_tuple[0] for gene_tuple in output]
#    #print output_list  
#    return output_list

def extract_subject_genotypes(db_path, names, ids):
    query = "select %s from %s" % (", ".join(names), config.genotype_table)
    if ids :
        query += " where id in (%s)" % ", ".join(ids)  
    return dbms_backend.execute_query(query, db_path)


def extract_genotypes_for_single_class_type(db_path, names, class_type, classes):
    
    values = {"genotypes" : ", ".join(names), "genotype_table" : config.genotype_table, 
              "class_table" : config.class_table, "class_type" : class_type, "classes" : ", ".join(classes)}
    
    query = """
    select %(genotypes)s from %(genotype_table)s
    where id in (
        select id from %(class_table)s
        where %(class_type)s in (%(classes)s)
    ) 
    """ % values 
    
    print query
    
#    query = """
#    select (?) from ? 
#    where id in (
#        select id from ?
#        where ? in (?)
#    )
#    """
#    values = (", ".join(names), config.genotype_table, config.class_table, class_type, ", ".join(classes))    
#    
    return dbms_backend.execute_query(query, db_path)

def extract_epistasis_data_for_single_class_type(db_path, table, names, class_type, classes, above_zero=False):
    
    values = {"names" : ", ".join(names), "table" : table, 
              "class_table" : config.class_table, "class_type" : class_type, "classes" : ", ".join(classes)}
    
    
    query = """
    select %(names)s from %(table)s
    where id in (
        select id from %(class_table)s
        where %(class_type)s in (%(classes)s)
    ) """ % values 
    
    if above_zero:
        conditions = [x+" > 0" for x in names]
        all_above_zero = " and ".join(conditions)
        query += " and "+ all_above_zero 
    
    return dbms_backend.execute_query(query, db_path)

def extract_epistasis_data_for_single_class_type2(db_path, names, class_type, classes, above_zero=False):
    
    values = {"names" : ", ".join(names), "genotype_table" : config.genotype_table, "phenotype_table" : config.phenotype_table, 
              "class_table" : config.class_table, "class_type" : class_type, "classes" : ", ".join(classes)}
    
    
    query = """
    select %(names)s from %(genotype_table)s g, %(phenotype_table)s p
    where g.id=p.id and g.id in (
        select id from %(class_table)s
        where %(class_type)s in (%(classes)s)
    ) """ % values 
    
    if above_zero:
        conditions = [x+" > 0" for x in names]
        all_above_zero = " and ".join(conditions)
        query += " and "+ all_above_zero 
    
    

    return dbms_backend.execute_query(query, db_path)


def extract_column_names(db_path, table_name):
    return dbms_backend.extract_column_names(db_path, table_name)


def extract_from_table(db_path, table_name):
    query = "select * from %s" % table_name  
    return dbms_backend.execute_query(query, db_path)
    
def extract_phenotypes(db_path):
    query = "select * from %s" % config.phenotype_table  
    return dbms_backend.execute_query(query, db_path)
        
def extract_classes(db_path, names):
    query = "select %s from %s" % (", ".join(names), config.class_table)
    #print query
      
    return dbms_backend.execute_query(query, db_path)


def extract_distinct_classes(db_path, names):
    query = "select distinct %s from %s" % (", ".join(names), config.class_table)
    #print query
    #if names:
    #    query += " where id in (%s)" % tuple(names)
    
      
    return dbms_backend.execute_query(query, db_path)


def extract_subjects(db_path):
    query = "select * from %s" % config.subject_table  
    return dbms_backend.execute_query(query, db_path)


def drop_table(db_path, table_name):
    query = "drop table %s" % table_name  
    return dbms_backend.execute_query(query, db_path)
    
    
def table_size(db_path, table_name):
    return dbms_backend.table_size(db_path, table_name)
    
def start_db(db_path):
    return dbms_backend.start_db(db_path)    

def shutdown_db(db_path, kill=False):
    dbms_backend.close_db(db_path, kill)
    

    

    